﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace mediatheque.Areas.Admin.Models
{
    public class Oeuvres : mediathequeEntities
    {
        public List<movies> movieData;
        public List<oeuvre> getOeuvres()
        {
            var list = from o in this.oeuvres select o;
            return list.ToList();
        }

        public List<user> getAuthor()
        {
            var list = from a in this.users select a;
            return list.ToList();
        }
                
        public List <movies> getMoviesById(int id)
        {
            var list = from m in this.movies
                       where m.id == id
                       select m;
            this.movieData = list.ToList();
            return this.movieData;
        }

        public List<movies> getMovies()
        {
            var list = from m in this.movies
                       orderby m.title ascending
                       select m;
            return list.ToList();
        }

        public List<movies> getLastMovies()
        {
            var list = from m in this.movies
                       orderby m.title ascending
                       select m;
            return list.ToList();
        }

        public List<categories> getCategories()
        {
            var list = from c in this.categories
                       orderby c.lib ascending
                       select c;
            return list.ToList();
        }

        public List<categories> getCategoriesForMovie(int movie)
        {
            var list = from cat in categories
                       join cg in categorized
                       on cat.id equals cg.category
                       select cat;

            return list.ToList();
        }

        public List<artiste> getArtistes()
        {
            var list = from a in artiste
                       orderby a.firstname ascending
                       select a;
            return list.ToList();
        }

        public List<artiste> getRealForMovie(int id)
        {
            var list = from a in artiste
                       join p in participate
                       on a.id equals p.artiste 
                       where p.type == "dvd"
                       where p.lib == "real"
                       where p.media == id
                       orderby a.firstname ascending
                       select a;
            return list.ToList();
        }

        public List<artiste> getActorForMovie(int id)
        {
            var list = from a in artiste
                       join p in participate
                       on a.id equals p.artiste
                       where p.type == "dvd"
                       where p.lib == "actor"
                       where p.media == id
                       orderby a.firstname ascending
                       select a;
            return list.ToList();
        }

        public int getStockForMovie(int id)
        {
            var list = (from emp in emprunts
                        where emp.date_emprunt != null
                        where emp.date_retour == null
                        where emp.media == id
                        where emp.type == "dvd"
                       select new {count = emprunts.Count() }).ToList();
            return list.Count();
        }

        public bool isBorrowed(int user, int media, string type)
        {
            var select = (from emp in emprunts
                          where emp.media == media
                          where emp.type == type
                          where emp.user == user
                          where emp.back == null
                          select emp.id).ToList();
            var test = select.Count() > 0;
            return test;

        }
    }
}